#coding:utf-8
import pymysql
import sys

import re
import os
import json
from conf import Conf
from common import *
import time
from bs4 import BeautifulSoup
from html_table_parser import parser_functions as parse
import numpy as np

def data_key_ls(data_dict):
    data_key_ls_end = []
    if isinstance(data_dict, str):
        return []
    for i in data_dict.keys():
        if (i[:5] == 'table') or (i[:4] == 'data'):
            data_key_ls_end = data_key_ls_end + data_key_ls(data_dict[i])
        else:
            try:
                if 'original' in data_dict[i]:
                    data_key = data_dict[i]['original']
                    if len(data_key) <= 20:
                        data_key_ls_end.append(data_key)
            except:
                continue
    return data_key_ls_end
from queue import Queue



def judge(word):

    if re.search('^\d+\.?\d+\w{1,2}$',word):
        word = ''
    if re.search('^\w{1,2}[\d.,，]+$',word):
        return ''
    if re.search('^\d+\.?\d+日历天$',word):
        return ''
    if re.search('[\d,，]{6,}',word):
        return ''
    company_chose = '((调查|设计总?|研究总?|勘[察查探测](测绘)?|水电学|轻工|工程|规划|雕塑)院|(监理|[检监]测|咨询|信息|发展|开发|经销|服务|研究|采购|地质|分|绿化|园艺|评估|维修|经营|养护)中心|(检|监)(测|验)总?站|工程(处|总队)|(水文|工程|建设|资源|造林|林业|分|体育|教育)局|(设计|事务|研究|监理|管理)所|(经(销|营)|修理)部|(监理|工作)站|(化探|地质|[一二三四五六七八九十]+)队|管理处|合作社|(林|园艺|养殖)场|设计室|公司|科技行|学会|大学|商城|(印刷|修配|加工)厂|汽车会所|家具城|学校)'
    if re.search(company_chose, word):
        return ''
    if word == '':
        return ''
    if word[-1] in ['乡','镇']:
        return ''

    if len(word) >= 20:
        return ''
    namelist = [
            "丁",
            "万",
            "东",
            "严",
            "丰",
            "乌",
            "乐",
            "乔",
            "习",
            "于",
            "云",
            "井",
            "仇",
            "从",
            "仲",
            "任",
            "伊",
            "伍",
            "何",
            "余",
            "侯",
            "俞",
            "倪",
            "傅",
            "储",
            "元",
            "充",
            "党",
            "全",
            "公",
            "关",
            "养",
            "冀",
            "冉",
            "农",
            "冯",
            "冷",
            "凌",
            "凤",
            "刁",
            "刘",
            "别",
            "劳",
            "包",
            "匡",
            "华",
            "卓",
            "卜",
            "卞",
            "卢",
            "卫",
            "印",
            "危",
            "厉",
            "双",
            "古",
            "史",
            "叶",
            "司",
            "吉",
            "向",
            "吕",
            "吴",
            "周",
            "和",
            "咸",
            "唐",
            "喻",
            "国",
            "夏",
            "奚",
            "姚",
            "姜",
            "姬",
            "娄",
            "孔",
            "孙",
            "孟",
            "季",
            "宁",
            "安",
            "宋",
            "宗",
            "宣",
            "宦",
            "宫",
            "家",
            "容",
            "宿",
            "寇",
            "富",
            "寿",
            "封",
            "尚",
            "尤",
            "尹",
            "居",
            "屈",
            "屠",
            "山",
            "崔",
            "嵇",
            "巢",
            "左",
            "巩",
            "巫",
            "巴",
            "师",
            "席",
            "常",
            "干",
            "平",
            "幸",
            "广",
            "庄",
            "应",
            "庞",
            "康",
            "廉",
            "廖",
            "弓",
            "张",
            "强",
            "彭",
            "徐",
            "惠",
            "慎",
            "慕",
            "戈",
            "戎",
            "成",
            "戚",
            "戴",
            "房",
            "扶",
            "支",
            "敖",
            "文",
            "方",
            "於",
            "施",
            "时",
            "昌",
            "明",
            "易",
            "昝",
            "晏",
            "景",
            "暴",
            "曲",
            "曹",
            "曾",
            "朱",
            "权",
            "李",
            "杜",
            "束",
            "杨",
            "杭",
            "松",
            "林",
            "柏",
            "查",
            "柯",
            "柳",
            "柴",
            "栾",
            "桂",
            "桑",
            "梁",
            "梅",
            "樊",
            "欧",
            "武",
            "段",
            "殷",
            "毕",
            "毛",
            "水",
            "江",
            "池",
            "汤",
            "汪",
            "沃",
            "沈",
            "沙",
            "洪",
            "浦",
            "温",
            "游",
            "湛",
            "滑",
            "滕",
            "满",
            "潘",
            "濮",
            "焦",
            "熊",
            "燕",
            "牛",
            "牧",
            "狄",
            "王",
            "班",
            "璩",
            "甄",
            "甘",
            "田",
            "申",
            "白",
            "皮",
            "盖",
            "盛",
            "相",
            "瞿",
            "石",
            "祁",
            "祖",
            "祝",
            "禹",
            "秋",
            "秦",
            "程",
            "穆",
            "空",
            "窦",
            "章",
            "童",
            "竺",
            "符",
            "简",
            "管",
            "米",
            "索",
            "红",
            "纪",
            "经",
            "缪",
            "罗",
            "羊",
            "翁",
            "翟",
            "耿",
            "聂",
            "胡",
            "胥",
            "臧",
            "舒",
            "艾",
            "芮",
            "花",
            "苍",
            "苏",
            "苗",
            "范",
            "茅",
            "荀",
            "荆",
            "荣",
            "莘",
            "莫",
            "萧",
            "葛",
            "董",
            "蒋",
            "蒲",
            "蓝",
            "蔚",
            "蔡",
            "蔺",
            "薄",
            "薛",
            "虞",
            "融",
            "衡",
            "袁",
            "裘",
            "裴",
            "褚",
            "解",
            "詹",
            "计",
            "许",
            "诸",
            "谈",
            "谢",
            "谭",
            "谷",
            "贝",
            "贡",
            "费",
            "贺",
            "贾",
            "赖",
            "赵",
            "路",
            "车",
            "辛",
            "边",
            "连",
            "逄",
            "通",
            "逯",
            "邓",
            "邢",
            "邬",
            "邰",
            "邱",
            "邵",
            "邹",
            "郁",
            "郎",
            "郏",
            "郑",
            "郜",
            "郝",
            "郦",
            "郭",
            "都",
            "钟",
            "钭",
            "钱",
            "闵",
            "闻",
            "阎",
            "阚",
            "阮",
            "阴",
            "陆",
            "陈",
            "陶",
            "隆",
            "雍",
            "雷",
            "霍",
            '敬',
            "靳",
            "鞠",
            "韦",
            "韩",
            "项",
            '付',
            "顾",
            "颜",
            "饶",
            "马",
            "骆",
            "高",
            "魏",
            "鱼",
            "鲁",
            "鲍",
            "麻",
            "黄",
            "黎",
            "齐",
            "龙",
            "龚"
        ]
    if word[0] in namelist and len(word) <= 3:
        return ''
    if re.search('公司$',word) and len(word) > 5:
        return ''
    ls = ['[¥￥※]$','[0-9]{4}年[0-9]{1,2}月','\*\*','\.zip$','\.docx$','（略）$','(老师|女士|先生|局)$','^(\d+\.?)+%?（?万?元?）?$','\.jpg$']
    for i in ls:
        if re.search(i, word):
            return ''
    return word

def read_html_table(value):
    result = {}
    tables = BeautifulSoup(value, "lxml").findAll('table')
    for idx, v in enumerate(tables):
        if v.findParent('table') is None:
            result[idx] = parse.make2d(v)
    return result

def replace_word_use(word):
    if word == '':
        return word
    word = word.replace('?','？')
    word = word.replace('(', '（')
    word = word.replace(')', '）')
    word = word.replace(':', '：')
    word = word.split('：')[0]
    word = word.replace(' ', '')
    word = word.replace('？', '')
    word = word.replace('*', '')
    key = True
    while True:
        obj = re.search('（(\d+)分）$',word)
        if obj:
            key = False
            word = word.replace(obj.group(1),'')

        obj = re.search(r'（([^（]*?)）$', word)
        obj1 = re.search('（分）$', word)
        if obj and obj1 == None:
            key = False
            tt = obj.group(1)
            word = word.replace('（' + tt + '）','')
        sub_list = ['？', ':', '：', '/', '\\', '>', '<', '，', '。', '、', ';', '￥', '|']
        if word == '':
            return word
        if word[0] in sub_list:
            key = False
            word = word[1:]
        if word == '':
            return word
        if word[-1] in sub_list:
            key = False
            word = word[:-1]
        if key:
            break
        else:
            key = True

    ls = ['\d{1,2}[.、]', '[一二三四五六七八九十]{1,2}[.、]', '（[一二三四五六七八九十]）', '（\d{1,2}）']
    ls = ['^' + i + '(.*)' for i in ls]
    for i in ls:
        word = re.sub(i, '\\1', word)
    word = word.split('：')[0]
    return word

def flaten(array):
    for item in array:
        if isinstance(item, list):
            yield from flaten(item)
        else:
            yield item

class Db():
    def __init__(self,dbname):
        self.conn = pymysql.connect(
            host='192.168.0.160',
            port=3306,
            user='root',
            passwd='lubanjianye',
            charset='utf8',
            database=dbname,
            cursorclass=pymysql.cursors.DictCursor
        )
    def __enter__(self):
        return self.conn
    def __exit__(self,a,b,c):
       print(a,'/',b,'/',c)
       self.conn.close()


def to_mysql(data_key_ls_end,conn):

    cursor = conn.cursor()
    sql = f'insert ignore into dispose.analyze_key(key_name,url,tag_flag) values(%s,%s,%s)'
    conn.ping(reconnect=True)
    cursor.executemany(sql,data_key_ls_end)
    conn.commit()


name_queue = Queue()
father_path_yesanalyze = 'E:\\project\\DataBaseData\\YesAnalyze'
type_dir = ['3','5']

for typee in type_dir:
    date_dir = os.listdir(os.path.join(father_path_yesanalyze, typee))
    for data_source_id in date_dir:
        tt = []
        data_source_id_dir = os.listdir(os.path.join(father_path_yesanalyze, typee, data_source_id))
        for date in data_source_id_dir:
            zip_dir = os.listdir(os.path.join(father_path_yesanalyze, typee, data_source_id, date))
            for zipp in zip_dir:
                tt.append([typee, data_source_id, date, zipp])
                if len(tt) == 3:
                    break
            if len(tt) == 3:
                break
        for tti in tt:
            name_queue.put(tti)
        tt = []
count = 0
count_all = name_queue.qsize()
data_ls = []
with Db('data_collection_server') as conn:
    cursor = conn.cursor()
    while not name_queue.empty():
        data = name_queue.get()
        typee, data_source_id, date, zipp = data
        judge_id = int(re.split('-_', data_source_id)[0])
        sql = f'select table_name from id_to_name_handle where data_source_id = {judge_id}'
        cursor.execute(sql)
        dataii = cursor.fetchone()
        if dataii == None:
            continue
        dataii = dataii['table_name']
        if '采购' in dataii:
            jugde_name_key = True
        else:
            jugde_name_key = False
        lujin = os.path.join(father_path_yesanalyze,typee, data_source_id, date, zipp)
        zipkey = iscompletezip(os.path.join(father_path_yesanalyze,typee, data_source_id, date, zipp))
        if zipkey:
            continue
        read_data = Readzip(lujin)
        for json_data in read_data:
            title = json_data['title']
            if not re.search('(建设|治理|维修|提升|排查|基础设施)项目|工程|施工|(土地|综合)(整治|治理)|建设|监理|修建|造价|咨询|安装|装修|改造提升',title):
                continue
            url = json_data['url']
            html = json_data['html_content']
            a = list(read_html_table(html).values())
            data_key_ls_end = set(flaten(a))
            data_key_ls_end = list(map(judge, list(data_key_ls_end)))
            data_key_ls_end = list(map(replace_word_use,data_key_ls_end))
            data_key_ls_end = list(set(data_key_ls_end))
            if '' in data_key_ls_end:
                data_key_ls_end.remove('')
            logger.info(url)
            data_ls += [(i,url,3) for i in data_key_ls_end]
            if len(data_ls) >= 1000:
                to_mysql(data_ls,conn)
                data_ls = []
                logger.info(lujin)
        count += 1
        logger.info('{0}/{1}'.format(count,count_all))

